/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Server.DAO;

import DTO.PhieuHuHai_DTO;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author Lenh Ho Xung
 */
public class PhieuHuHai_DAO {
     private static String sqlString;
    private static MSSQLConnect cn;
    private static ResultSet rs;

    public static DefaultTableModel addTable(ResultSet rs) throws Exception {
        DefaultTableModel table = new DefaultTableModel();
        ResultSetMetaData metaData = rs.getMetaData();
        int totalColumn = metaData.getColumnCount();
        Object[] dataRow = new Object[totalColumn];

        if (rs != null) {
            for (int i = 1; i <= totalColumn; i++) {
                table.addColumn(metaData.getColumnName(i));
            }
            while (rs.next()) {
                for (int i = 1; i <= totalColumn; i++) {
                    dataRow[i - 1] = rs.getObject(i);
                }
                table.addRow(dataRow);
            }

        }
        return table;

    }
    public static DefaultTableModel layDanhSach() throws Exception {
        DefaultTableModel table = new DefaultTableModel();
        sqlString = "SELECT MaPhieuHuHai as [Mã phiếu hư hại], MaKhach as [Mã khách],"
                + " NoiDungHuHai as [Nội dung hư hại], TriGia as [Trị giá]"           
                + "  FROM PHIEUHUHAI";

        try {
            //thực hiện câu truy vấn
            cn = new MSSQLConnect();
            //thực hiện câu truy vấn
            rs = cn.excuteQuery(sqlString);
            table = addTable(rs);
        } catch (Exception e) {
            throw new Exception("Không thể lấy danh sách phòng");
        } finally {
            cn.Close();
        }
        return table;

    }
     public static int Insert(PhieuHuHai_DTO p) throws Exception {
        int n = 0;
        sqlString = "INSERT INTO PhieuHuHai (MaPhieuHuHai, MaKhach, NoiDungHuHai,TriGia) VALUES('"
                + p.getMaphieuhuHai() + "','"
                + p.getMaKhach() + "',N'"
                + p.getNoidunghuHai() + "','"          
                + p.getTriGia() + "')";

        //Kết nối CSDL
        try {
            cn = new MSSQLConnect();
            n = cn.executeUpdate(sqlString);
        } catch (Exception e) {
            System.out.print(e.toString());
            //throw new Exception("Không thể lấy danh sách phòng");
        } finally {
            cn.Close();
        }
        return n;
    }
     public static int Delete(String maphieuhuHai) throws Exception {
        int n;
        sqlString = "DELETE FROM PHIEUHUHAI WHERE MaPhieuHuHai='" + maphieuhuHai + "'";
        try {
            cn = new MSSQLConnect();
            //Thực thi câu lệnh SQL
            n = cn.executeUpdate(sqlString);

        } catch (Exception e) {
            throw new Exception("Lỗi Kết Nối Cơ sở dữ liệu.");
        } finally {
            cn.Close();
        }
        return n;
    }
     public static int Update(PhieuHuHai_DTO p) throws Exception {
        int n;
        sqlString = "UPDATE PHONG SET TenPhong='" + p.getMaKhach()
                + "',GhiChu=N'" + p.getNoidunghuHai()
                + "',TinhTrang=N'" + p.getNoidunghuHai()            
                + "' WHERE MaPhong='" + p.getMaphieuhuHai() + "'";
        try {
            cn = new MSSQLConnect();
            //Thực thi câu lệnh SQL
            n = cn.executeUpdate(sqlString);

        } catch (Exception e) {
            throw new Exception("Lỗi Kết Nối Cơ sở dữ liệu.");
        } finally {
            cn.Close();
        }
        return n;
    }
      public static String laymaMax() throws Exception {
        String maphieuMax = new String();
        sqlString = "SELECT TOP 1 MaPhieuHuHai FROM PhieuHuHai ORDER BY MaPhieuHuHai DESC";
        try {
            cn = new MSSQLConnect();
            rs = cn.excuteQuery(sqlString);
            while (rs.next()) {
                maphieuMax = rs.getString("MaPhieuHuHai");
            }
        } catch (Exception e) {
            throw new Exception("Không thể lấy mã max");

        } finally {
            cn.Close();
        }
        return maphieuMax;
    }
       public static Float layTriGiaTheoMaKhach(String maKhach) throws Exception{
         Float trigia = 0f;
        sqlString = "SELECT TriGia FROM PhieuHuHai where MaKhach= '"+maKhach+"'";
                
        try {
            cn = new MSSQLConnect();
            rs = cn.excuteQuery(sqlString);
            while (rs.next()) {
                trigia = rs.getFloat("TriGia");
            }
        } catch (Exception e) {
            throw new Exception("Không thể lấy trị giá phiếu thuê");

        } finally {
            cn.Close();
        }
        return trigia;
    }
    
}
